package com.scalar.chiptrack.operations.dataentry.dao;

import com.scalar.chiptrack.operations.dataentry.LotDetails;
import com.scalar.chiptrack.adaptor.AdaptorConstants;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;

import com.scalar.ScalarException;


import java.util.ArrayList;
import java.sql.*;


public class ScrapMaterialDAO implements AdaptorConstants
{
        
    public static ArrayList getScrapProcessTypes()
    {
        ArrayList processTypes = new ArrayList();

        /*processTypes.add( new org.apache.struts.util.LabelValueBean( LOCATION_TYPE_WAFER_BANK, LOCATION_TYPE_WAFER_BANK ) );*/
        processTypes.add( new org.apache.struts.util.LabelValueBean( LOCATION_TYPE_DIE_BANK, LOCATION_TYPE_DIE_BANK ) );
        processTypes.add( new org.apache.struts.util.LabelValueBean( LOCATION_TYPE_ASSEMBLY, LOCATION_TYPE_ASSEMBLY ) );
        processTypes.add( new org.apache.struts.util.LabelValueBean( LOCATION_TYPE_FT, LOCATION_TYPE_FT ) );
        processTypes.add( new org.apache.struts.util.LabelValueBean( LOCATION_TYPE_TAPE_REEL, LOCATION_TYPE_TAPE_REEL ) );
        processTypes.add( new org.apache.struts.util.LabelValueBean( LOCATION_TYPE_FG, LOCATION_TYPE_FG ) );

        return processTypes;
    }

    public static void saveScrappedLotDetails ( DBTransaction dbTransaction, ArrayList scrapLotsList, String userName ) throws Exception
    {
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails invScrapLots = null;

        if ( ( scrapLotsList == null) || (scrapLotsList.isEmpty() ) )
        {
            return;
        }
    	try
        {
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}


            pstmt = conn.prepareStatement( " { CALL SP_CREATE_UPDATE_SCRAPPED_LOT (?, ?, ?, ?, ?, ? ) }" );

            for (int i = 0; i < scrapLotsList.size(); i++)
            {
                invScrapLots = (LotDetails) scrapLotsList.get(i);

                if ( invScrapLots.isChecked() )
                {                                           
                    pstmt.setString(1, invScrapLots.getLotId() );
                    pstmt.setInt(2, invScrapLots.getScrapDieQty() ); //SCRAP QTY
                    pstmt.setString(3, invScrapLots.getCheckedWaferIdsAsString() );
                    pstmt.setString(4, invScrapLots.getScrapComments() );
                    pstmt.setInt(5, invScrapLots.isNonValueInventory() ? 0 : 1 );
                    pstmt.setString(6, userName );

                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
            success = true;

        }
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not save Scrapped SetUp from database", sqle);
		}
		finally
		{
			if ( dbTransaction == null )
			{
				try
				{
					if (conn != null)
					{
                        if( success == true )
                        {
						    conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
					}
				}
                catch (SQLException ignore) {System.out.println("Sql exception ");}
			}

			if ( pstmt != null )
			{
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
    }

    public static void updateDieBankScrappedBinInfo (  DBTransaction dbTransaction, ArrayList dieBankWaferBinInfoList ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        PreparedStatement pstmt1 = null;
        boolean success         = false;
        LotDetails sortBinWaferLots= null;
        String scrapBinQtyArray[]  = null;
        if ( ( dieBankWaferBinInfoList == null) || (dieBankWaferBinInfoList.isEmpty() ) )
        {
            return;
        }

        try
        {
            conn = DataSource.getConnection();

            //WHEN SHIPPED FOR OTHER PROCESS
            pstmt1 = conn.prepareStatement("UPDATE SORT_BIN_RELEASE_TABLE SET BIN_1_BALANCE_QTY = BIN_1_BALANCE_QTY - ? WHERE LOT_ID = ? AND WAFER_ID = ?");

            for (int i = 0; i < dieBankWaferBinInfoList.size(); i++)
            {
                sortBinWaferLots = (LotDetails) dieBankWaferBinInfoList.get(i);

                if ( sortBinWaferLots.getCheckedWaferIdsAsString() != null )
                {
                    String waferArray[] = sortBinWaferLots.getCheckedWaferIdsAsString().split(";");
                    scrapBinQtyArray = sortBinWaferLots.getCheckedScrapQtyAsString().split(";");

                    for ( int j = 0; j < waferArray.length; j++)
                    {
                        pstmt1.setInt(1, Integer.parseInt(scrapBinQtyArray[j] ));
                        pstmt1.setInt(2, Integer.parseInt(sortBinWaferLots.getLotId() ) );
                        pstmt1.setInt(3, Integer.parseInt(waferArray[j]) );

                        pstmt1.execute();
                    }
                }
            }

            //pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not update Sort bin release Lot information", sqle);
        }

        finally
        {
             try
             {
                if (conn != null)
                {
                    if (success == true)
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore)
            {
            }

            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }

    public static void moveNonValueInvetoryToWip ( DBTransaction dbTransaction, ArrayList scrapLotsList, String userName ) throws Exception
    {
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails invScrapLots = null;

        if ( ( scrapLotsList == null) || (scrapLotsList.isEmpty() ) )
        {
            return;
        }
    	try
        {
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            pstmt = conn.prepareStatement( " { CALL SP_CREATE_UPDATE_NONVALUE_INVENTORY_LOT (?, ?, ?, ?, ?, ? ) }" );

            for (int i = 0; i < scrapLotsList.size(); i++)
            {
                invScrapLots = (LotDetails) scrapLotsList.get(i);

                if ( invScrapLots.isChecked() )
                {                       
                    pstmt.setString(1, invScrapLots.getLotId() );
                    pstmt.setString(2, invScrapLots.getDieQtyIn() ); //WIP QTY
                    pstmt.setString(3, invScrapLots.getDateIn() );
                    pstmt.setString(4, invScrapLots.getCheckedWaferIdsAsString() );
                    pstmt.setString(5, invScrapLots.getScrapComments() );
                    pstmt.setString(6, userName );

                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
            success = true;

        }
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not save Scrapped SetUp from database", sqle);
		}
		finally
		{
			if ( dbTransaction == null )
			{
				try
				{
					if (conn != null)
					{
                        if( success == true )
                        {
						    conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
					}
				}
                catch (SQLException ignore) {System.out.println("Sql exception ");}
			}

			if ( pstmt != null )
			{
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
    }
    public static void moveNonValueInvetoryToScrap ( DBTransaction dbTransaction, ArrayList scrapLotsList, String userName ) throws Exception
    {
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails invScrapLots = null;

        if ( ( scrapLotsList == null) || (scrapLotsList.isEmpty() ) )
        {
            return;
        }
    	try
        {
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            pstmt = conn.prepareStatement( " { CALL SP_CREATE_UPDATE_NONVALUE_SCRAP_LOT (?, ?, ?, ?, ?, ? ) }" );

            for (int i = 0; i < scrapLotsList.size(); i++)
            {
                invScrapLots = (LotDetails) scrapLotsList.get(i);

                if ( invScrapLots.isChecked() )
                {
                    pstmt.setString(1, invScrapLots.getLotId() );
                    pstmt.setString(2, invScrapLots.getDieQtyIn() ); //WIP QTY
                    pstmt.setString(3, invScrapLots.getDateIn() );
                    pstmt.setString(4, invScrapLots.getCheckedWaferIdsAsString() );
                    pstmt.setString(5, invScrapLots.getScrapComments() );
                    pstmt.setString(6, userName );

                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
            success = true;

        }
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not move NonValue Inventory as Scrapped Inventory", sqle);
		}
		finally
		{
			if ( dbTransaction == null )
			{
				try
				{
					if (conn != null)
					{
                        if( success == true )
                        {
						    conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
					}
				}
                catch (SQLException ignore) {System.out.println("Sql exception ");}
			}

			if ( pstmt != null )
			{
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
    }

    public static void updateDieBankNonValueBinInfo (  DBTransaction dbTransaction, ArrayList dieBankWaferBinInfoList ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        PreparedStatement pstmt1 = null;
        boolean success         = false;
        LotDetails sortBinWaferLots= null;
        String scrapBinQtyArray[]  = null;
        if ( ( dieBankWaferBinInfoList == null) || (dieBankWaferBinInfoList.isEmpty() ) )
        {
            return;
        }

        try
        {
            if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            //WHEN SHIPPED FOR OTHER PROCESS
            pstmt1 = conn.prepareStatement("UPDATE SORT_BIN_RELEASE_TABLE SET BIN_1_BALANCE_QTY = ISNULL(BIN_1_BALANCE_QTY, 0) - ? WHERE LOT_ID = ? AND WAFER_ID = ?");

            for (int i = 0; i < dieBankWaferBinInfoList.size(); i++)
            {
                sortBinWaferLots = (LotDetails) dieBankWaferBinInfoList.get(i);

                if ( sortBinWaferLots.getCheckedWaferIdsAsString() != null )
                {
                    String waferArray[] = sortBinWaferLots.getCheckedWaferIdsAsString().split(";");
                    scrapBinQtyArray = sortBinWaferLots.getCheckedScrapQtyAsString().split(";");

                    for ( int j = 0; j < waferArray.length; j++)
                    {                           
                        pstmt1.setInt(1, Integer.parseInt(scrapBinQtyArray[j] ));
                        pstmt1.setInt(2, Integer.parseInt(sortBinWaferLots.getLotId() ) );
                        pstmt1.setInt(3, Integer.parseInt(waferArray[j]) );

                        pstmt1.execute();
                    }
                }
            }

            //pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not update Sort bin release Lot information", sqle);
        }

        finally
        {
             try
             {
                if (conn != null)
                {
                    if (success == true)
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore)
            {
            }

            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }
 
    
}
